package dao;



import pojo.AirQualityIndex;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDaoImpl extends BaseDao implements BaoDao{

    //A. 按区域查询空气质量记录
    public List<AirQualityIndex> select(int districtId) {
        System.out.println("按区域查询空气质量记录");
        List<AirQualityIndex> list = new ArrayList<AirQualityIndex>();
        AirQualityIndex Aq = null;
        ResultSet rs = null;
        String sql = "select * from air_quality_index where districtId=? ";
        rs = this.executeQuery(sql, districtId);
        try {
            while (rs.next()) {
                       int Id= rs.getInt("id");
                String DistrictIds= rs.getString("districtId");
                       String monitorTime= rs.getString("monitorTime");
                       int Pm10= rs.getInt("pm10");
                       int Pm25=  rs.getInt("pm25");
                       String MonitoringStation=  rs.getString("monitoringStation");
                       String LastModifyTime= rs.getString("lastModifyTime");
                       Aq=new AirQualityIndex();
                         Aq.setId(Id);
                        Aq.setDistrictId(DistrictIds);
                        Aq.setMonitorTime(monitorTime);
                        Aq.setPm10(Pm10);
                        Aq.setPm25(Pm25);
                        Aq.setMonitoringStation(MonitoringStation);
                        Aq.setLastModifyTime(LastModifyTime);
                        list.add(Aq);
//                System.out.println("记录编号:"+Id);
//                System.out.println("区域编号:"+DistrictIds);
//                System.out.println("监测时间:"+monitorTime);
//                System.out.println("pm10值:"+Pm10);
//                System.out.println("pm2.5值:"+Pm25);
//                System.out.println("监测站:"+MonitoringStation);
//                System.out.println("最后更改时间:"+LastModifyTime);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return list;
    }

    //B. 添加新的空气质量记录
    public boolean add(AirQualityIndex aq) {
        boolean flag = false;
        int rs =0;
        try {
            String sql = "insert into air_quality_index(districtId,monitorTime,pm10,pm25,monitoringStation,lastModifyTime) values(?,?,?,?,?,?)";
//            String sql = "insert into air_quality_index(id,districtId,monitorTime,pm10,pm25,monitoringStation,lastModifyTime) values(?,?,?,?,?,?,?)";
            rs = this.exceuteUpdate(sql);
            Object[] params = {aq.getDistrictId(),aq.getMonitorTime(),aq.getPm10(),aq.getPm25(),aq.getMonitoringStation(),aq.getLastModifyTime()};
            int i = this.exceuteUpdate(sql,params);
            // 4 处理执行结果
            if (i > 0) {
                System.out.println("添加新的空气质量记录成功！");
                flag=true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn,pstmt,null);
        }
        return flag;
    }

    //C. 根据 name 查找指定的记录
    public  List<AirQualityIndex> selecByName(String name)  {
        System.out.println("根据 ID 查找指定的记录");
        List<AirQualityIndex> list = new ArrayList<AirQualityIndex>();
        AirQualityIndex Aq = null;
        ResultSet rs = null;
        String sql = "select * from air_quality_index where districtId=?";
        rs = this.executeQuery(sql, name);
        try {
            while (rs.next()) {
                int Id= rs.getInt("id");
                String DistrictIds= rs.getString("districtId");
                String monitorTime= rs.getString("monitorTime");
                int Pm10= rs.getInt("pm10");
                int Pm25=  rs.getInt("pm25");
                String MonitoringStation=  rs.getString("monitoringStation");
                String LastModifyTime= rs.getString("lastModifyTime");
                Aq=new AirQualityIndex();
                Aq.setId(Id);
                Aq.setDistrictId(DistrictIds);
                Aq.setMonitorTime(monitorTime);
                Aq.setPm10(Pm10);
                Aq.setPm25(Pm25);
                Aq.setMonitoringStation(MonitoringStation);
                Aq.setLastModifyTime(LastModifyTime);
                list.add(Aq);
//                System.out.println("记录编号:"+Id);
//                System.out.println("区域编号:"+DistrictIds);
//                System.out.println("监测时间:"+monitorTime);
//                System.out.println("pm10值:"+Pm10);
//                System.out.println("pm2.5值:"+Pm25);
//                System.out.println("监测站:"+MonitoringStation);
//                System.out.println("最后更改时间:"+LastModifyTime);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return list;
    }

    //C. 根据 ID 查找指定的记录
    public  List<AirQualityIndex> selecById(int id)  {
        System.out.println("根据 ID 查找指定的记录");
        List<AirQualityIndex> list = new ArrayList<AirQualityIndex>();
        AirQualityIndex Aq = null;
        ResultSet rs = null;
        String sql = "select * from air_quality_index where id=?";
        rs = this.executeQuery(sql, id);
        try {
            while (rs.next()) {
                int Id= rs.getInt("id");
                String DistrictIds= rs.getString("districtId");
                String monitorTime= rs.getString("monitorTime");
                int Pm10= rs.getInt("pm10");
                int Pm25=  rs.getInt("pm25");
                String MonitoringStation=  rs.getString("monitoringStation");
                String LastModifyTime= rs.getString("lastModifyTime");
                Aq=new AirQualityIndex();
                Aq.setId(Id);
                Aq.setDistrictId(DistrictIds);
                Aq.setMonitorTime(monitorTime);
                Aq.setPm10(Pm10);
                Aq.setPm25(Pm25);
                Aq.setMonitoringStation(MonitoringStation);
                Aq.setLastModifyTime(LastModifyTime);
                list.add(Aq);
                System.out.println("记录编号:"+Id);
                System.out.println("区域编号:"+DistrictIds);
                System.out.println("监测时间:"+monitorTime);
                System.out.println("pm10值:"+Pm10);
                System.out.println("pm2.5值:"+Pm25);
                System.out.println("监测站:"+MonitoringStation);
                System.out.println("最后更改时间:"+LastModifyTime);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return list;
    }

    //D. 修改指定记录
    public int update(AirQualityIndex aq){
        /*String sql = "update air_quality_index set id=?," +
                "monitorTime=?," +
                "pm10=?," +
                "pm25=?," +
                "monitoringStation=?," +
                "lastModifyTime=?," +
                "where districtId=?";*/
        String sql = "update air_quality_index set districtId=?,monitorTime=?,pm10=?,pm25=?,monitoringStation=?,lastModifyTime=? where id=?";
        Object[] params = {
                aq.getDistrictId(),
                aq.getMonitorTime(),
                aq.getPm10(),
                aq.getPm25(),
                aq.getMonitoringStation(),
                aq.getLastModifyTime(),
                aq.getId()

        };
        return this.exceuteUpdate(sql,params);
    }

    //E. 删除指定记录
    public boolean del(int id) {
        boolean flag = false;
        String sql = "DELETE FROM air_quality_index WHERE id=?";
        Object [] params = {id};
        int i = this.exceuteUpdate(sql,params);
        if (i>0){
            System.out.println("删除成功");
            flag=true;
        }
        return flag;
    }

    //F. 查询所有区域信息
    public List<AirQualityIndex> selectAll()  {
        System.out.println("查询所有区域信息");
        List<AirQualityIndex> list = new ArrayList<AirQualityIndex>();
        AirQualityIndex Aq = null;
        ResultSet rs = null;
        String sql = "select * from air_quality_index";
        rs = this.executeQuery(sql);
        try {
            while (rs.next()) {
                int Id= rs.getInt("id");
                String DistrictIds= rs.getString("districtId");
                String monitorTime= rs.getString("monitorTime");
                int Pm10= rs.getInt("pm10");
                int Pm25=  rs.getInt("pm25");
                String MonitoringStation=  rs.getString("monitoringStation");
                String LastModifyTime= rs.getString("lastModifyTime");
                Aq=new AirQualityIndex();
                Aq.setId(Id);
                Aq.setDistrictId(DistrictIds);
                Aq.setMonitorTime(monitorTime);
                Aq.setPm10(Pm10);
                Aq.setPm25(Pm25);
                Aq.setMonitoringStation(MonitoringStation);
                Aq.setLastModifyTime(LastModifyTime);
                list.add(Aq);
//                System.out.println("记录编号:"+Id);
//                System.out.println("区域编号:"+DistrictIds);
//                System.out.println("监测时间:"+monitorTime);
//                System.out.println("pm10值:"+Pm10);
//                System.out.println("pm2.5值:"+Pm25);
//                System.out.println("监测站:"+MonitoringStation);
//                System.out.println("最后更改时间:"+LastModifyTime);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return list;
    }

    public static void main(String[] args) {
        AirQualityIndex a=new AirQualityIndex();
        UserDaoImpl aq =new UserDaoImpl();
//        Date nowTime = new Date(System.currentTimeMillis());
//        String time = nowTime.toString();
//        System.out.println(time);
        a.setId(13);
        a.setDistrictId("西城区");
        a.setMonitorTime("2022-2-14");
        a.setPm10(22);
        a.setPm25(23);
        a.setMonitoringStation("测试点8");
        a.setLastModifyTime("2022-2-14");

        //测试按区域查询空气质量记录成功  aq.select(8);
        //测试添加记录成功
//         aq.add(a);
        //测试指定Name查询成功    aq.selecBy测试指定Name查询成功(6);
//        测试指定ID查询成功
//    aq.selecById(14);
//        aq.update(a);
        //测试删除成功    aq.del(7);
        //测试所有区域成功    aq.selectAll();



    }
















}
